package com.gitee.loyo.jdbc

import com.gitee.loyo.*
import org.junit.After
import org.junit.Before
import org.junit.Test
import org.junit.Assert.*
import java.sql.Connection

class JDBCTestCase : H2TestCase(){

    @Before
    fun init(){
        KSQL.tryGetConnection = {
            Conn.current() ?: Conn.create(JDBC.ds()?.connection ?: throw NullPointerException("未发现DataSource")).also {
                it.executeOnce = true
            }
        }
        dataSource.connection.use { conn ->
            conn.createStatement().use { stmt ->
                stmt.addBatch("""
                    insert into t_user(user_id, user_name, sex, active)values(1, 'admin1', 1, 0)
                """.trimIndent())
                stmt.addBatch("""
                    insert into t_user(user_id, user_name, sex, active)values(2, 'admin2', 2, 0)
                """.trimIndent())
                stmt.addBatch("""
                    insert into t_job(employee_id, user_id, title, salary)values(1, 1, 'staff1', 1234.50)
                """.trimIndent())
                stmt.addBatch("""
                    insert into t_job(employee_id, user_id, title, salary)values(2, 2, 'staff2', 5678.49)
                """.trimIndent())

                stmt.executeBatch()
            }
            conn.commit()
        }
        JDBC.init(dataSource)
    }

    @After
    fun close(){
        dataSource.connection.use {
            it.createStatement().use {
                it.execute("truncate table t_user")
                it.execute("truncate table t_job")
                it.execute("truncate table t_user_profile")
            }
            it.commit()
        }
    }

    @Test
    fun testQueryList(){
        val userList = JDBC["default"].open { conn ->
            conn.queryList<TUser>("select * from t_user")
        }
        assertEquals(2, userList.size)
    }

    @Test
    fun testQueryPairList(){
        val userList = JDBC.open { conn ->
            conn.queryList<Pair<TUser, Count>>("select user_id, user_name, count(*) number from t_user group by user_name order by user_id, user_name")
        }
        assertEquals(2, userList.size)
        assertEquals("admin1", userList[0].first.userName)
        assertEquals(1, userList[0].second.number)
        assertEquals("admin2", userList[1].first.userName)
        assertEquals(1, userList[1].second.number)
    }

    @Test
    fun testQueryTripeList(){
        val user = JDBC.open { conn ->
            conn.queryList<Triple<Long, String, Int>>("select user_id, user_name, sex number from t_user where user_id = 1")
        }
        assertEquals(1L, user?.get(0).first)
        assertEquals("admin1", user?.get(0).second)
        assertEquals(1, user?.get(0).third)
    }

    @Test
    fun testQueryPage(){
        val (count, list) = JDBC.open { conn ->
            conn.queryPage<TUser>("select * from t_user").limit(0, 5)
        }
        assertEquals(2, count)
        assertEquals(2, list.size)
    }

    @Test
    fun testQueryOne(){
        val user = JDBC.open { conn ->
            conn.queryOne<TUser>("select user_id, user_name name, sex from t_user where user_id = ?", 1L)
        }
        assertNotNull(user)
        assertEquals(1L, user?.userId)
        assertNull(user?.userName)
        assertEquals(1, user?.sex)
        assertEquals(true, user?.active)


        val noUser = JDBC.open { conn ->
            conn.queryOne<TUser>("select * from t_user where user_id = ?", 99L)
        }
        assertNull(noUser)
    }

    @Test
    fun testQueryMergeObject(){
        val merge = JDBC.open { conn ->
            conn.queryOne<MergeObject>("""
                select 
                a.user_id "user.userId", a.user_name "user.userName", 0 "user.active", 
                b.employee_id "employee.employeeId", b.title "employee.title"
                from t_user a, t_job b
                where a.user_id = ? and a.user_id = b.user_id
            """.trimIndent(), 1L)
        }
        assertNotNull(merge)
        assertEquals(1L, merge?.user?.userId)
        assertEquals("admin1", merge?.user?.userName)
        assertEquals(false, merge?.user?.active)
        assertEquals(1L, merge?.employee?.employeeId)
        assertEquals("staff1", merge?.employee?.title)


        val noUser = JDBC.open { conn ->
            conn.queryOne<TUser>("select * from t_user where user_id = ?", 99L)
        }
        assertNull(noUser)
    }

    @Test
    fun testInsert(){
        val newUserId = JDBC.open { conn ->
            conn.insertForGeneratedKey("insert into t_user(user_name, sex)values(?, ?)", "admin3", 1)
        }
        assertNotNull(newUserId)
    }

    @Test
    fun testRollback(){
        val size = JDBC.open(Connection.TRANSACTION_READ_COMMITTED){ conn ->
            conn.update("insert into t_user(user_name, sex)values(?, ?)", "admin3", 1)
            conn.rollback()
            conn.query("select count(*) from t_user").asInt()
        }
        assertEquals(2, size)
    }

    @Test
    fun testBatch(){
        val size = JDBC.open { conn ->
            conn.batch("insert into t_user(user_name, sex)values(?, ?)", listOf(listOf("admin3", 1), listOf("admin4", 2)))
            conn.query("select count(*) from t_user").`as`<Int>()
        }
        assertEquals(4, size)
    }

    @Test
    fun testUpdate(){
        val succeed = JDBC.open { conn ->
            conn.update("update t_user set user_name = ? where user_name like ?", "admin3", "admin%") == 2
        }
        assertTrue(succeed)
    }

    @Test
    fun testMapperQueryList(){
        val userList = JDBC.open { conn ->
            conn.mapper<TUserMapper>().queryUsers("admin", 1)
        }
        assertEquals(1, userList.size)
    }

    @Test
    fun testMapperQueryPairList(){
        JDBC.open { conn ->
            conn.mapper<TUserMapper>().queryPairList()
        }.forEachIndexed { index, (userId, userName) ->
            when(index){
                0 -> {
                    assertEquals(1, userId)
                    assertEquals("admin1", userName)
                }
                1 -> {
                    assertEquals(2, userId)
                    assertEquals("admin2", userName)
                }
            }
        }
    }

    @Test
    fun testMapperQueryTripleList(){
        JDBC.open { conn ->
            conn.mapper<TUserMapper>().queryTripleList()
        }.forEachIndexed { index, (userId, userName, sex) ->
            when(index){
                0 -> {
                    assertEquals(1, userId)
                    assertEquals("admin1", userName)
                    assertEquals(1, sex)
                }
                1 -> {
                    assertEquals(2, userId)
                    assertEquals("admin2", userName)
                    assertEquals(2, sex)
                }
            }
        }
    }

    @Test
    fun testMapperQueryOne(){
        val TUser = JDBC.open { conn ->
            conn.mapper<TUserMapper>().getUserById(1L)
        }
        assertNotNull(TUser)
        assertEquals(1L, TUser!!.userId)
        assertEquals("admin1", TUser!!.userName)
        assertEquals(1, TUser!!.sex)
    }

    @Test
    fun testMapperQueryOnePair(){
        val (userId, userName) = JDBC.open { conn ->
            conn.mapper<TUserMapper>().getPairById(1L)!!
        }
        assertEquals(1, userId)
        assertEquals("admin1", userName)
    }

    @Test
    fun testMapperQueryOneTriple(){
        val (userId, userName, sex) = JDBC.open { conn ->
            conn.mapper<TUserMapper>().getTripleById(1L)!!
        }
        assertEquals(1, userId)
        assertEquals("admin1", userName)
        assertEquals(1, sex)
    }

    @Test
    fun testMapperUpdate(){
        val succeed = JDBC.open { conn ->
            conn.mapper<TUserMapper>().update(TUser(1L, "adminX", 3)) == 1
        }
        assertTrue(succeed)
    }

    class MergeObject{
        var user: TUser? = null
        var employee: TEmployee? = null
    }
}